﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace Accountability4net.MySQLDbProvider
{
    public class MySQLDbTransaction: BaseTransaction
    {
        MySQLDbProvider provider_;
        protected MySQLDbTransaction()
            : base()
        {
        }

        public MySQLDbTransaction(MySQLDbProvider provider)
            : this()
        {
            provider_ = provider;
        }

        public void WirePropertyChanged()
        {
            PropertyChanged += MySQLDbTransaction_PropertyChanged;
        }

        void MySQLDbTransaction_PropertyChanged(object sender, System.ComponentModel.PropertyChangedEventArgs e)
        {
            string sql = "UPDATE `transaction` SET `Description` = @description WHERE `Id` = @id";
            using (var command = new MySqlCommand(sql, provider_.Connection))
            {
                MySqlParameter idParameter = new MySqlParameter("@id", ID.ToString());
                command.Parameters.Add(idParameter);
                MySqlParameter descriptionParameter = new MySqlParameter("@description", Description);
                command.Parameters.Add(descriptionParameter);
                int rows = command.ExecuteNonQuery();
            }
        }

        public static MySQLDbTransaction Create(MySQLDbProvider provider, DateTimeOffset timestamp)
        {
            MySQLDbTransaction transaction = new MySQLDbTransaction(provider);
            transaction.Timestamp = timestamp;
            transaction.ID = Guid.NewGuid();
            transaction.WirePropertyChanged();
            string sql = "INSERT INTO `transaction` (`Id`, `Timestamp`, `Commited`) VALUES (@id, @timestamp, @commited)";
            using (var command = new MySqlCommand(sql, provider.Connection))
            {
                MySqlParameter idParameter = new MySqlParameter("@id", transaction.ID.ToString());
                command.Parameters.Add(idParameter);
                MySqlParameter timestampParameter = new MySqlParameter("@timestamp", timestamp.UtcTicks);
                command.Parameters.Add(timestampParameter);
                MySqlParameter commitedParameter = new MySqlParameter("@commited", false);
                command.Parameters.Add(commitedParameter);
                int rows = command.ExecuteNonQuery();
            }
            return transaction;
        }

        private DateTimeOffset timestamp_;
        public override DateTimeOffset Timestamp
        {
            get
            {
                return timestamp_;
            }
            set
            {
                timestamp_ = value;
            }
        }

        private string description_;
        public override string Description
        {
            get
            {
                return description_;
            }
            set
            {
                description_ = value;
                OnPropertyChanged("Description");
            }
        }

        private bool isCommited_;
        public override bool IsCommitted
        {
            get
            {
                return isCommited_;
            }
        }

        private Guid id_;
        public override Guid ID
        {
            get
            {
                if (id_ == null)
                {
                    id_ = Guid.NewGuid();
                }
                return id_;
            }
            set
            {
                id_ = value;
            }
        }

        public override void Commit()
        {
            using (var transaction = provider_.Connection.BeginTransaction())
            {
                try
                {
                    base.Commit();
                    foreach (IAction action in this)
                    {
                        if (action.Type == ActionType.CREDIT)
                        {
                            action.Account.Credit(action.Amount, this);
                        }
                        else if (action.Type == ActionType.DEBIT)
                        {
                            action.Account.Debit(action.Amount, this);
                        }
                    }
                    var sql = "UPDATE `transaction` SET `Commited` = 1 WHERE `Id` = @id";
                    using(var command = new MySqlCommand(sql, transaction.Connection, transaction))
                    {
                        var idParameter = new MySqlParameter("@id", ID.ToString());
                        command.Parameters.Add(idParameter);
                        int rows = command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                    isCommited_ = true;
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
                finally
                {
                }
            }
        }

        public static IList<ITransaction> Retrieve(MySQLDbProvider provider)
        {
            var result = new List<ITransaction>();
            string sql = "SELECT * FROM `Transaction` WHERE `Commited` = 1";
            using (var command = new MySqlCommand(sql, provider.Connection))
            {
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var transaction = new MySQLDbTransaction(provider);
                        transaction.ID = new Guid(reader.GetString("Id"));
                        transaction.WirePropertyChanged();
                        result.Add(transaction);
                    }
                    reader.Close();
                }
            }
            return result;
        }

        public override void Add(IAction action)
        {
            if (!(action is MySQLDbAction))
            {
                throw new ArgumentException("Parameter must be of type MySQLAdoDbAction", "action");
            }
            string sql = "UPDATE `action` SET `transaction_id` = @transaction_id WHERE `id` = @action_id";
            using (var command = new MySqlCommand(sql, provider_.Connection))
            {
                MySqlParameter transactionIdParameter = new MySqlParameter("@transaction_id", ID.ToString());
                command.Parameters.Add(transactionIdParameter);
                MySqlParameter actionIdParameter = new MySqlParameter("@action_id", ((MySQLDbAction)action).Id);
                command.Parameters.Add(actionIdParameter);
                int rows = command.ExecuteNonQuery();
            }
            base.Add(action);
        }

        public static IList<ITransaction> Retrieve(MySQLDbProvider provider, IAccount account)
        {
            var result = new List<ITransaction>();
            string sql = "SELECT t.* FROM `Transaction` t, `account`, `action`"
                +" WHERE `Commited` = 1"
                +" AND `action`.`transaction_id` = t.Id"
                +" AND `action`.`account_id` = `account`.`Number`"
                +" AND `account`.`Number` = @account";
            using (var command = new MySqlCommand(sql, provider.Connection))
            {
                var accountParameter = new MySqlParameter("@account", account.Number);
                command.Parameters.Add(accountParameter);
                CreateFromCommand(provider, command)
                    .ToList()
                    .ForEach(t => {
                        MySQLDbAction.Retrieve(provider, account, t)
                            .ToList()
                            .ForEach(a => t.Add(a));
                        result.Add(t);
                    });
            }
            return result;
        }

        public static IList<ITransaction> Retrieve(MySQLDbProvider provider, IAccount account, DateTimeOffset? after)
        {
            var result = new List<ITransaction>();
            string sql = "SELECT t.* FROM `Transaction` t, `account`, `action`"
                + " WHERE `Commited` = 1"
                + " AND `action`.`transaction_id` = t.Id"
                + " AND `action`.`account_id` = `account`.`Number`"
                + " AND `account`.`Number` = @account";
            using (var command = new MySqlCommand(sql, provider.Connection))
            {
                var accountParameter = new MySqlParameter("@account", account.Number);
                command.Parameters.Add(accountParameter);
                AddAfterCondition(command, after);
                CreateFromCommand(provider, command)
                    .ToList()
                    .ForEach(t =>
                    {
                        MySQLDbAction.Retrieve(provider, account, t)
                            .ToList()
                            .ForEach(a => t.Add(a));
                        result.Add(t);
                    });
            }
            return result;

        }

        public static IList<ITransaction> Retrieve(MySQLDbProvider provider, IAccount account, DateTimeOffset? after, DateTimeOffset? before)
        {
            var result = new List<ITransaction>();
            string sql = "SELECT t.* FROM `Transaction` t, `account`, `action`"
                + " WHERE `Commited` = 1"
                + " AND `action`.`transaction_id` = t.Id"
                + " AND `action`.`account_id` = `account`.`Number`"
                + " AND `account`.`Number` = @account";
            using (var command = new MySqlCommand(sql, provider.Connection))
            {
                var accountParameter = new MySqlParameter("@account", account.Number);
                command.Parameters.Add(accountParameter);
                AddAfterCondition(command, after);
                AddBeforeCondition(command, before);
                CreateFromCommand(provider, command)
                    .ToList()
                    .ForEach(t => {
                        MySQLDbAction.Retrieve(provider, account, t)
                            .ToList()
                            .ForEach(a => t.Add(a));
                        result.Add(t);
                    });
            }
            return result;
        }

        private static void AddBeforeCondition(MySqlCommand command, DateTimeOffset? before)
        {
            MySqlParameter beforeParameter;
            if (before.HasValue)
            {
                command.CommandText += " AND t.`Timestamp` <= @before";
                beforeParameter = new MySqlParameter("@before", MySqlDbType.Int64);
                beforeParameter.Value = before.Value.UtcTicks;
                command.Parameters.Add(beforeParameter);
            }
        }

        private static void AddAfterCondition(MySqlCommand command, DateTimeOffset? after)
        {
            MySqlParameter afterParameter;
            if (after.HasValue)
            {
                command.CommandText += " AND t.`Timestamp` > @after";
                afterParameter = new MySqlParameter("@after", MySqlDbType.Int64);
                afterParameter.Value = after.Value.UtcTicks;
                command.Parameters.Add(afterParameter);
            }
        }

        private static IEnumerable<MySQLDbTransaction> CreateFromCommand(MySQLDbProvider provider, MySqlCommand command)
        {
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    var transaction = new MySQLDbTransaction(provider);
                    transaction.ID = new Guid(reader.GetString("Id"));
                    // MySqlDataReader.GetString would give NullReferenceException if Description is null
                    transaction.Description = reader.GetValue(reader.GetOrdinal("Description")) as String;
                    transaction.WirePropertyChanged();
                    yield return transaction;
                }
            }
        }
    }
}
